package com.zretc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.zretc.dao.UserDao;
import com.zretc.entity.UserInfo;
import com.zretc.entity.CollectionInfo;
import com.zretc.entity.CommentInfo;
import com.zretc.entity.CommentRelation;
import com.zretc.util.DBUtil;

public class UserDaoImpl implements UserDao{
	
	@Override
	public int insertUserInfo(UserInfo userInfo) {
		String sql = "insert into userinfo(user_name,user_password,user_sex,user_phone,user_picture) values(?,?,?,?,?)";
		int n = DBUtil.doUpdate(sql, userInfo.getUserName(),userInfo.getUserPassword(),userInfo.getUserSex(),userInfo.getUserPhone(),userInfo.getUserPicture());
		return n;
	}
	/**
	 * 将该房源添加到收藏夹
	 * @author liumeiqi777
	 * @param collectionInfo
	 * @return 影响行数
	 */
	@Override
	public int insertCollectionInfo(CollectionInfo collectionInfo) {
		int n = DBUtil.doUpdate("insert into collectioninfo values(?,?,?)", collectionInfo.getCollectionId(),collectionInfo.getUserInfo().getUserId(),null);
		return n;
	}
	 /**
     * 删除收藏夹中的房源信息
     * @author liumeiqi777
     * @param collectionInfo
     * @return
     */
	@Override
	public int deleteCollectionInfo(CollectionInfo collectionInfo) {
		int n = DBUtil.doUpdate("delete from collectioninfo where collection_id = ?",collectionInfo.getCollectionId());
		return n;
	}
	/**
     * 获取收藏夹中指定用户的所有收藏ID
     * @author liumeiqi777
     * @param userId
     * @return 收藏编号列表
     */
	@Override
	public List<String> listCollectionIds(Integer userId) {
		List<String> list = new ArrayList<String>();
		ResultSet doQuery = DBUtil.doQuery("select collection_id from collectioninfo where user_id = ? ", userId);
		try {
			while(doQuery.next()) {
				String collectionId = doQuery.getString("collection_id");
				list.add(collectionId);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
     * 为指定订单添加一条评论
     * @author liumeiqi777
     * @param commentInfo
     * @return
     */
	@Override
	public int insertCommentInfo(CommentInfo commentInfo) {
		int n = DBUtil.doUpdate("insert into commentinfo values(?,?,?,?,?,?,?)"
				                 ,commentInfo.getUserInfo().getUserId()
				                 ,commentInfo.getCommentId()
				                 ,commentInfo.getCommentTime()
				                 ,commentInfo.getCommentContent()
				                 ,commentInfo.getCommentScore()
				                 ,commentInfo.getCommentRelation().getCommentRelationId()
				                 ,commentInfo.getOrderInfo().getOrderId());
		return n;
	}
	 /**
     * 为指定评论添加一条回复
     * @author liumeiqi777
     * @param commentRelation
     * @return
     */
	@Override
	public int insertCommentRelation(CommentRelation commentRelation) {
		int n = DBUtil.doUpdate("insert into commentrelation values(?,?,?)"
				                 ,commentRelation.getCommentRelationId()
				                 ,commentRelation.getCommentId()
				                 ,commentRelation.getParentId());
		return n;
	}
	@Override
	public UserInfo findUserInfoByName(String userName) {
		UserInfo userInfo = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select")
		   .append(" user_id,")
		   .append(" user_name,")
		   .append(" user_password,")
		   .append(" user_sex,")
		   .append(" user_phone,")
		   .append(" user_picture")
		   .append(" from userinfo")
		   .append(" where user_name = ?"); 
		
		ResultSet rs = DBUtil.doQuery(sql.toString(), userName);
		
		try {
			while (rs.next()) {
				int userId = rs.getInt("user_id");
				userName = rs.getString("user_name");
				String userPassword = rs.getString("user_password");
				String userSex = rs.getString("user_sex");
				String userPhone = rs.getString("user_phone");
				String userPicture = rs.getString("user_picture");
				
				userInfo = new UserInfo(userId, userName, userPassword, userSex, userPhone, userPicture);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userInfo;
	}

	@Override
	public UserInfo findUserInfoByNameAndPwd(String userName, String userPassword) {
		UserInfo userInfo = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select")
		   .append(" user_id,")
		   .append(" user_name,")
		   .append(" user_password,")
		   .append(" user_sex,")
		   .append(" user_phone,")
		   .append(" user_picture")
		   .append(" from userinfo")
		   .append(" where user_name = ? and user_password = ?"); 
		
		ResultSet rs = DBUtil.doQuery(sql.toString(), userName, userPassword);
		
		try {
			while (rs.next()) {
				int userId = rs.getInt("user_id");
				userName = rs.getString("user_name");
				userPassword = rs.getString("user_password");
				String userSex = rs.getString("user_sex");
				String userPhone = rs.getString("user_phone");
				String userPicture = rs.getString("user_picture");
				
				userInfo = new UserInfo(userId, userName, userPassword, userSex, userPhone, userPicture);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userInfo;
	}
	/**
     * 查询指定民宿的评论
     * @author liumeiqi777
     * @param roomId
     * @return
     */
	@Override
	public List<Map<String, String>> listCommentInfos(String roomId) {
		List<Map<String, String>> list = new ArrayList<>();
		StringBuffer sb = new StringBuffer();
		//根据房间编号查询该房间的相关一级评论，且评论关系id为空
		sb.append("select")
		.append(" b.user_name")
		.append(",a.comment_id")
		.append(",a.comment_score")
		.append(",a.comment_content")
		.append(",a.comment_time")
		.append(" from commentinfo a inner join userinfo b on a.user_id = b.user_id ")
		.append(" inner join orderinfo c on c.order_id = a.order_id")
		.append(" where comment_relation_id = 1")
		.append(" and c.room_id = ?");
		 ResultSet doQuery = DBUtil.doQuery(sb.toString(), roomId);
		 try {
			while(doQuery.next()) {
				 String commentId = doQuery.getString("comment_id"); 
				 String userName = doQuery.getString("user_name");
				 String commentScore = doQuery.getString("comment_score");
				 String commentContent = doQuery.getString("comment_content");
				 String commentTime = doQuery.getString("comment_time");
				 Map<String, String> map = new HashMap<>();
				 map.put("commentId", commentId);
                 map.put("userName", userName);
                 map.put("commentScore",commentScore);
                 map.put("commentContent", commentContent);
                 map.put("commentTime", commentTime);
                 list.add(map);
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return list;
	}
	@Override
	public List<Map<String, String>> listCommentInfoChildrens(String parentId) {
		List<Map<String, String>> list = new ArrayList<>();
		StringBuffer sb = new StringBuffer();
		//根据房间编号查询该房间的相关一级评论，且评论关系id为空
		sb.append("select")
		.append(" b.user_name")
		.append(",a.comment_id")
		.append(",a.comment_score")
		.append(",a.comment_content")
		.append(",a.comment_time")
		.append(" from commentinfo a left join userinfo b on a.user_id = b.user_id ")
		.append(" left join orderinfo c on c.order_id = a.order_id")
		.append(" left join commentrelation d on d.comment_id = a.comment_id")
		.append(" where d.parent_id = ?");
		 ResultSet doQuery = DBUtil.doQuery(sb.toString(), parentId);
		 try {
			while(doQuery.next()) {
				 String userName = doQuery.getString("user_name");
				 String commentId = doQuery.getString("comment_id");
				 String commentScore = doQuery.getString("comment_score");
				 String commentContent = doQuery.getString("comment_content");
				 String commentTime = doQuery.getString("comment_time");
				 Map<String, String> map = new HashMap<>();
				 map.put("commentId", commentId);
                 map.put("userName", userName);
                 map.put("commentScore",commentScore);
                 map.put("commentContent", commentContent);
                 map.put("commentTime", commentTime);
                 list.add(map);
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return list;
	}
	/**
     * 为指定评论添加一条回复内容
     * @author liumeiqi777
     * @param commentInfo
     * @return
     */
	@Override
	public int insertCommentReply(CommentInfo commentInfo) {
		int n = DBUtil.doUpdate("insert into commentinfo values(?,?,?,?,null,?,null)"
                ,commentInfo.getUserInfo().getUserId()
                ,commentInfo.getCommentId()
                ,commentInfo.getCommentTime()
                ,commentInfo.getCommentContent()
                ,commentInfo.getCommentRelation().getCommentRelationId());
         return n;
	}

	@Override
	public int updateUserPassword(String userNo, String password) {
		int n = DBUtil.doUpdate("update userinfo set user_password = ? where user_id = ?",password,userNo);
		return n;
	}
}
